package com.jackrain.nea.web.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.util.TypeUtils;
import com.jackrain.nea.core.query.ColumnLink;
import com.jackrain.nea.core.query.QueryException;
import com.jackrain.nea.core.schema.Column;
import com.jackrain.nea.core.schema.SQLTypes;
import com.jackrain.nea.core.schema.Table;
import com.jackrain.nea.core.schema.TableManager;
import com.jackrain.nea.exception.NDSException;
import com.jackrain.nea.service.AjaxUtils;
import com.jackrain.nea.util.ApplicationContextHandle;
import com.jackrain.nea.util.ShiroSessionUtil;
import com.jackrain.nea.util.Tools;
import com.jackrain.nea.util.ValueHolder;
import com.jackrain.nea.web.face.User;
import com.jackrain.nea.web.query.*;
import com.jackrain.nea.web.security.SecurityWebUtils;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

@Slf4j
@RestController
@Api(value = "ExportController", description = "数据导出")
public class ExportController {


    private final static int MAX_REPORT_LINES= 65535; //最大导出行数

    /**
     * From 2.0, add two parameter in event:
     * "request" - QueryRequest of the original query
     */
    @RequestMapping(path = "/p/cs/export",method = RequestMethod.POST)
    public JSONObject export(HttpServletRequest request,
                             @RequestParam(value = "searchdata", required = true) String searchdata,
                             @RequestParam(value = "filename", required = true) String fileName,
                             @RequestParam(value = "filetype", required = true) String fileType,
                             @RequestParam(value = "showColumnName", required = true) boolean showColumnName) throws Exception {

        User user = (User) ShiroSessionUtil.getAttribute("user");
        QuerySessionImpl session = new QuerySessionImpl(request);
        TableManager tm = session.getTableManager();
        String location = ResourceUtils.getURL("classpath:").getPath();
        location = location + "export" + File.separator + user.getId() + File.separator;

        File svrDir = new File(location);
        if (!svrDir.isDirectory()) {
            svrDir.mkdirs();
        }
        String fullFileName = location + fileName + fileType;

        JSONObject jo=JSON.parseObject(searchdata);
        Table table = null;
        int tableid=jo.getIntValue("tableid") ;

        if(tableid>0&&table==null) {
            table = tm.getTable(tableid);
        }
        if(jo.getString("table")!=null&&table==null) {
            table = tm.getTable(jo.getString("table"));
        }
        if(table==null) {
            throw  new NDSException("tablenull");
        }

        QueryRequest query= AjaxUtils.parseQuery(jo, session, user.getId(), user.getLocale(),table);
        QueryResult qr= QueryEngine.getInstance().doQuery( query);
        //列头
        JSONArray headers = query.SelectbyDisObj(true,user.getLocale());

        //表数据返回
        JSONObject result=qr.getColumnDataObj(true);
        JSONArray rows = result.getJSONArray("row");
        int totalRowCount = result.getIntValue("totalRowCount");

        int max_rowcount = 10;
        int pages = totalRowCount/max_rowcount;
        if(totalRowCount % max_rowcount != 0){
            pages = pages + 1;
        }

        try {

            if (fileType.endsWith(".xls")) {
                // inp = new FileInputStream(FilePath);
                Workbook wb = (Workbook) new HSSFWorkbook();
                Sheet sheet = wb.createSheet(table.getName());

                CellStyle headStyle = getStyle(wb,true,false,"STRING");
                CellStyle strStyle = getStyle(wb,false,false,"STRING");
                CellStyle numStyle = getStyle(wb,false,false,"NUMBER");

                Row excel_row;
                Cell cell;
                if (showColumnName) {
                    excel_row = sheet.createRow(0);
                    for (int i = 0; i < headers.size(); i++) {
                        JSONObject header = headers.getJSONObject(i);
                        cell = excel_row.createCell((short) i);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(headStyle);
                        String colName = header.getString("name");
                        cell.setCellValue(colName);
                        sheet.setColumnWidth((short)i,(colName.getBytes().length*256));

                    }
                }

                for(int page=0; page < pages; page++){
                    jo.put("range",max_rowcount);
                    jo.put("startindex",max_rowcount*page);
                    query= AjaxUtils.parseQuery(jo, session, user.getId(), user.getLocale(),table);
                    qr= QueryEngine.getInstance().doQuery( query);

                    result=qr.getColumnDataObj(true);
                    rows = result.getJSONArray("row");

                    for(int row=0; rows != null && row<rows.size(); row++) {
                        JSONObject rs = (JSONObject) rows.get(row);
                        excel_row = sheet.createRow(page*max_rowcount+row+1);
                        for (int col = 0; col < headers.size(); col++) {
                            cell = excel_row.createCell(col);
                            JSONObject header = headers.getJSONObject(col);
                            switch (header.getString("type")) {
                                case "STRING":
                                    if(rs.get(header.getString("colname")) != null){
                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        cell.setCellValue(s);
                                    }
                                    break;
                                case "NUMBER":
                                    if((header.getString("display").equals("select") ||
                                            header.getString("display").equals("check")) &&
                                              rs.get(header.getString("colname")) != null){

                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        cell.setCellValue(s);

                                    } else if(rs.get(header.getString("colname")) != null) {
                                        Double d = rs.getJSONObject(header.getString("colname")).getDouble("val");
                                        cell.setCellValue(d);
                                        cell.setCellStyle(numStyle);
                                    }
                                    break;
                                default:
                                    log.debug("Find at cell(" + (page*max_rowcount+row+1) + "," + (col + 1) + ") type is invalid");
                            }

                        }
                    }

                }
                FileOutputStream fileOut = new FileOutputStream(fullFileName);
                wb.write(fileOut);
                fileOut.close();

            } else if (fileType.endsWith(".xlsx")) {

                SXSSFWorkbook wb = new SXSSFWorkbook(100);
                wb.setCompressTempFiles(true);
                Sheet sheet = wb.createSheet(table.getName());

                CellStyle headStyle = getStyle(wb,true,false,"STRING");
                CellStyle strStyle = getStyle(wb,false,false,"STRING");
                CellStyle numStyle = getStyle(wb,false,false,"NUMBER");

                Row excel_row;
                Cell cell;
                if (showColumnName) {
                    excel_row = sheet.createRow(0);
                    for (int i = 0; i < headers.size(); i++) {
                        JSONObject header = headers.getJSONObject(i);
                        cell = excel_row.createCell((short) i);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(headStyle);
                        String colName = header.getString("name");
                        cell.setCellValue(colName);
                        sheet.setColumnWidth((short)i,(colName.getBytes().length*256));

                    }
                }

                for(int page=0; page < pages; page++) {

                    jo.put("range", max_rowcount);
                    jo.put("startindex", max_rowcount * page);

                    query= AjaxUtils.parseQuery(jo, session, user.getId(), user.getLocale(),table);
                    qr= QueryEngine.getInstance().doQuery( query);

                    result = qr.getColumnDataObj(true);
                    rows = result.getJSONArray("row");


                    for(int row=0; rows != null && row<rows.size(); row++) {
                        JSONObject rs = (JSONObject) rows.get(row);
                        excel_row = sheet.createRow(page*max_rowcount+row+1);
                        for (int col = 0; col < headers.size(); col++) {
                            cell = excel_row.createCell(col);
                            JSONObject header = headers.getJSONObject(col);
                            switch (header.getString("type")) {
                                case "STRING":
                                    if(rs.get(header.getString("colname")) != null) {
                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        cell.setCellValue(s);
                                    }
                                    break;
                                case "NUMBER":

                                    if((header.getString("display").equals("select") ||
                                            header.getString("display").equals("check")) &&
                                             rs.get(header.getString("colname")) != null){

                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        cell.setCellValue(s);

                                    } else if(rs.get(header.getString("colname")) != null) {
                                        Double d = rs.getJSONObject(header.getString("colname")).getDouble("val");
                                        cell.setCellValue(d);
                                        cell.setCellStyle(numStyle);
                                    }
                                    break;
                                default:
                                    log.debug("Find at cell(" + (page*max_rowcount+row+1) + "," + (col + 1) + ") type is invalid");
                            }

                        }

                        if (row % 100 == 0) {
                            ((SXSSFSheet) sheet).flushRows(100); // retain 100 last rows and flush all others
                        }
                    }
                }

                FileOutputStream fileOut = new FileOutputStream(fullFileName);
                wb.write(fileOut);
                fileOut.close();
            }
        } catch (Exception e) {
            log.error("Could not export to excel file:" + location + File.separator + fileName, e);
            throw new NDSException(e.getMessage(),e);

        }
        ValueHolder valueHolder = new ValueHolder();
        valueHolder.put("code",0);
        valueHolder.put("data",fullFileName);
        return valueHolder.toJSONObject();

    }

    /**
     * @return column description concatenated by references.
     * 举例：要显示的是定单的申请人所在的部门名称，跨越的column是：
     * order.applierID, employee.departmentID, department.name
     * 对应的column名称分别是:申请人, 部门，名称。则合成的名称为：
     * 申请人部门名称
     */
    private  String[] getDisplayColumnNames(boolean showNullableIndicator, QueryRequest req,
                                            boolean pk, boolean ak, Locale locale,QuerySession session) throws QueryException {
        List<ColumnLink> selections = null;
        List<ColumnLink> qlcselections = null;

        int[] ids = req.getReportDisplayColumnIndices(pk, ak);
        selections = req.getAllSelectionColumnLinks();
        //support qlc
        DefaultWebEvent event = session.getEvent();
        int qlcId = Tools.getInt(event.getParameterValue("qlcid"), -1);
        log.debug("getDisplayColumnNames qlcid->" + qlcId);
        QueryListConfig qlc = null;
        if (qlcId > 0 && qlcId != -1) {
            qlc = QueryListConfigManager.getInstance().getQueryListConfig(qlcId,session.getTableManager());
            qlcselections = qlc.getSelections();
        }
        String[] dcns = new String[ids.length];
        ArrayList cls = req.getMainTable().getAllColumns();
        Column col = null;


        if (qlcselections != null && qlcselections.size() > 0) for (int j = 0; j < qlcselections.size(); j++) {
            log.debug("get equals: " + qlcselections.get(j));
        }
        for (int i = 0; i < ids.length; i++) {
            ColumnLink clink = (ColumnLink) selections.get(ids[i]);


            //log.debug(clink.toString());
            /**
             * 在主表中查找与clink 一致的字段，如果有，以主表字段名称显示
             * 9.34 add 关联名称输出 扩展属性{reflable:{"tabid":12853,"ref_id":1853}} 支持
             */
            if (qlcId < 1) {
                for (int j = 0; j < cls.size(); j++) {
                    col = (Column) cls.get(j);
                    ColumnLink colLink = col.getColumnLink(session.getTableManager());
                    JSONObject jo;
                    try {
                        jo = col.getJSONProps().getJSONObject("reflable");
                        int lable_id = jo.getIntValue("ref_id");
                        int lable_tabid = jo.getIntValue("tabid");
                        Table reftable = session.getTableManager().getTable(lable_tabid);
                        QueryRequestImpl query = new com.jackrain.nea.core.db.QueryRequestImpl(session);
                        query.setMainTable(lable_tabid);
                        query.addSelection(reftable.getAlternateKey().getId());
                        query.addParam(reftable.getPrimaryKey().getId(), "" + lable_id);
                        QueryResult rs = QueryEngine.getInstance().doQuery(query);
                        if (lable_id != 0 || (rs != null && rs.getTotalRowCount() > 0)) {
                            while (rs.next()) {
                                log.debug(rs.getObject(1).toString());
                                dcns[i] = rs.getObject(1).toString();
                            }
                        }
                        if (col.getReferenceColumn() != null) { // 对于 FK表，需要获取FK.AK
                            colLink.addColumn(col.getReferenceTable().getAlternateKey(),session.getTableManager());
                        }
                        if (clink.equals(colLink)) {
                            //dcns[i]= col.getDescription(locale);
                            //log.debug("get equals: "+ col);
                            break;
                        }
                        if (dcns[i] != null) continue;
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        // e.printStackTrace();
                        //log.debug("col get reflable is null");
                    }

                    if (col.getReferenceColumn() != null) { // 对于 FK表，需要获取FK.AK
                        colLink.addColumn(col.getReferenceTable().getAlternateKey(),session.getTableManager());
                    }
                    if (clink.equals(colLink)) {
                        dcns[i] = col.getDescription(session.getLocale());
                        //log.debug("get equals: "+ col);
                        break;
                    }
                }
            } else {
                log.debug("clink is: " + clink.getDescription(session.getLocale()));
                log.debug("clink last is: " + clink.getColumnLinkExcludeLastColumn(session.getTableManager()));
                log.debug("clink is: " + clink);

                if (qlcselections != null && qlcselections.size() > 0) {
                    dcns[i] = qlcselections.get(i).getDescription(session.getLocale());
                    log.debug("get equals: " + qlcselections.get(i).getDescription(session.getLocale()));

                }
//        	  for(int j=0;j< qlcselections.size();j++){
//        		  if( clink.equals(qlcselections.get(j))){
//            		  dcns[i]= qlcselections.get(j).getDescription(locale);
//            		  log.debug("get equals: "+ qlcselections.get(j).getDescription(locale));
//            		  break;
//            	  }
//        	  }
            }

            if (dcns[i] != null) continue;
            dcns[i] = "";
            int len = clink.getColumns().length;
            for (int j = 0; j < len; j++) {
                dcns[i] += clink.getColumns()[j].getDescription(session.getLocale()) + (j < len - 1 ? "." : "");
            }
            dcns[i] += (showNullableIndicator && !clink.getColumns()[0].isNullable()) ? "*" : " ";


        }
        return dcns;
    }

    private  Column[] getDisplayColumns(QueryRequest req, boolean pk, boolean ak) {
        int[] ids = req.getReportDisplayColumnIndices(pk, ak);
        ArrayList selections = req.getAllSelectionColumnLinks();
        Column[] cols = new Column[ids.length];
        for (int i = 0; i < ids.length; i++) {
            ColumnLink clink = (ColumnLink) selections.get(ids[i]);
            cols[i] = clink.getLastColumn();
        }
        return cols;

    }

    private  CellStyle getStyle(Workbook wb, boolean isBold,boolean isnotnull,String type) {
        // Create a new font and alter it.
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        if (isBold) {
            font.setColor((short) HSSFColor.DARK_BLUE.index);
            font.setBoldweight(font.BOLDWEIGHT_BOLD);
        }

        if (isnotnull) {
            font.setColor((short) HSSFColor.RED.index);
        }
        // Fonts are set into a style so create a new one to use.
        CellStyle style = wb.createCellStyle();
        style.setFont(font);

        DataFormat format = wb.createDataFormat();

        if(type.equalsIgnoreCase("STRING")){
            style.setDataFormat(format.getFormat("text"));
        }else if(type.equalsIgnoreCase("NUMBER")){
            style.setDataFormat(format.getFormat("General"));
        }

        return style;
    }



}
